package cn.hwua.dao.impl;

import cn.hwua.dao.FoodDao;
import cn.hwua.entity.Food;
import cn.hwua.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class FoodDaoImpl implements FoodDao {
    Connection conn=DBUtil.getConnection();
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(Food obj) {
        String sql="INSERT INTO  tb_food(food_id,food_name,food_img,food_price,food_address,food_addtime,food_add_user,food_video,food_content,food_sort) VALUES (?,?,?,?,?,?,?,?,?,?)";
        Object[] paras={obj.getFood_id(),obj.getFood_name(),obj.getFood_img(),obj.getFood_price(),obj.getFood_address(),
                obj.getFood_addtime(),obj.getFood_add_user(),obj.getFood_video(),obj.getFood_content(),obj.getFood_sort()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Food obj) {
        String sql="UPDATE  tb_food SET  food_name=?,food_img=?,food_price=?,food_address=?,food_addtime=?,food_add_user=?,food_video=?,food_content=?,food_sort=? WHERE food_id=? ";
        Object[] paras={obj.getFood_name(),obj.getFood_img(),obj.getFood_price(),obj.getFood_address(),obj.getFood_addtime(),
                obj.getFood_add_user(),obj.getFood_video(),obj.getFood_content(),obj.getFood_sort(),obj.getFood_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Food obj) {
        String sql="DELETE FROM tb_food WHERE food_id  = ?  ";
        Object[] paras={obj.getFood_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Food findById(Object id) {
        String sql="SELECT * FROM tb_food where food_id  = ?";
        Object[] paras={id};
        try {
            Food food=qr.query(conn,sql,paras,new BeanHandler<Food>(Food.class));
            return food;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Food> findAll() {
        String sql="SELECT * FROM tb_food";
        Object[] paras={};
        try {
            List<Food> foodList=qr.query(conn,sql,paras,new BeanListHandler<Food>(Food.class));
            return foodList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Food> findSearch(String keyword) {
        String sql="SELECT * FROM tb_food where food_name like concat('%',concat(?,'%'))";
        Object[] paras={keyword};
        try {
            List<Food> foodList=qr.query(conn,sql,paras,new BeanListHandler<Food>(Food.class));
            return foodList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Food> findPage(int page, int pagesize) {
        String sql="SELECT * FROM tb_food limit ?,?";
        Object[] paras={(page-1)*pagesize,pagesize};
        try {
            List<Food> foodList=qr.query(conn,sql,paras,new BeanListHandler<Food>(Food.class));
            return foodList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_food";
        Object[] paras={};
        try {
            long nums=qr.query(conn,sql,paras,new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Food> find(Map<String, Object> map) {
        String sql = "SELECT  * FROM tb_food where 1=1";
        if (null != map.get("food_name")) {
            sql += " and food_name like '%"+map.get("food_name").toString()+"%'";
        }
        if (null != map.get("food_sort")) {
            sql += " and food_sort = '"+map.get("food_sort").toString()+"'";
        }
        System.out.println("sql = " + sql);
        Object[] paras = {};
        try {
            List<Food> foodList = qr.query(conn, sql, paras, new BeanListHandler<Food>(Food.class));
            return foodList;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public List<String> findAddress() {
        String sql = "SELECT distinct food_sort FROM tb_food";
        Object[] paras = {};
        try {
            List<String> sortList = qr.query(conn, sql, paras, new ColumnListHandler<String>());
            return sortList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
